package com.suoluo.zenadmin.dao.dest;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;

import com.suoluo.zenadmin.vo.dest.DestFeature2VO;
import com.suoluo.zenadmin.vo.dest.DestVO;
import com.suoluo.zenadmin.vo.news.News2VO;

@DAO
public interface MobileDAO {
	
//	/**
//	 * 查询导航页特色类别为父类的特色分类信息
//	 * 
//	 * @return 特色分类为父类别的集合
//	 */
//	@SQL("select * from  tb_dest_feature_category "
//					+ "where t_feature_parent_category_id = 0 "
//					+ " and t_feature_category_id <> :1 order by t_feature_category_order asc")
//	public List<DestFeatureCategoryPOJO> findHomeParentFeatureCategory(int hotelCategoryId);
	
	@SQL("select * from (select " + 
			    "hc.t_feature_category_id, " +
			    "hc.t_category_name, " +
			    "hi.t_item_create_time, " +
			    "hi.t_link_id, " +
			    "hi.t_dest_id, " +
			    "dfi.zhname,dfi.enname, " +
			    "c.t_country_name_zh, " +
				"c.t_country_orig_flag, " +
			    "t6.source,t6.small,t6.normal,"+ 
			    "td.zhname destName " +
			"from tb_home_category hc " +
			"join tb_home_item hi ON hc.t_category_id = hi.t_category_id " +
			"join tb_dest_feature_item2 dfi ON dfi.feature_item_id = hi.t_link_id " +
			"join tb_country c on c.t_country_id = hi.t_country_id " +
			"join tb_dest td on td.dest_id = hi.t_dest_id " + 	
			"join tb_image t6 on t6.image_id = dfi.image_id " +
			"where hc.t_feature_category_id > 0  " +
			"union all select  " +
			    "hc.t_feature_category_id, " +
			    "hc.t_category_name, " +
			    "hi.t_item_create_time, " +
			    "hi.t_link_id, " +
			    "hi.t_dest_id, " +
			    "td.zhname,td.enname, " +
			    "c.t_country_name_zh, " +
				"c.t_country_orig_flag, " +
			    "t6.source,t6.small,t6.normal, " +
			    "null " +
			"from tb_home_category hc " +
			"join tb_home_item hi ON hc.t_category_id = hi.t_category_id " +
			"join tb_dest td ON hi.t_dest_id = td.dest_id " +
			"join tb_country c on c.t_country_id = hi.t_country_id " +
			"join tb_image t6 on t6.image_id = td.image_id "+
			"where hc.t_feature_category_id = - 2  " +
			"union all select  " +
			    "hc.t_feature_category_id, " +
			    "hc.t_category_name, " +
			    "hi.t_item_create_time, " +
			    "hi.t_link_id, " +
			    "hi.t_dest_id, " +
			    "h.zhname,h.enname, " +
			    "c.t_country_name_zh, " +
				"c.t_country_orig_flag, " +
			    "t6.source,t6.small,t6.normal, " +
			    "td.zhname destName " +
			"from tb_home_category hc " +
			"join tb_home_item hi ON hc.t_category_id = hi.t_category_id " +
			"join tb_hotel2 h ON hi.t_link_id = h.hotel_id " +
			"join tb_country c on c.t_country_id = hi.t_country_id " +
			"join tb_dest td on td.dest_id = hi.t_dest_id " + 	
			"join tb_image t6 on t6.image_id = h.image_id " +
			"where hc.t_feature_category_id = - 3  " +
			"union all select  " +
			    "hc.t_feature_category_id, " +
			    "hc.t_category_name, " +
			    "hi.t_item_create_time, " +
			    "hi.t_link_id, " +
			    "hi.t_dest_id, " +
			    "n.zhname,n.enname, " +
			    "c.t_country_name_zh, " +
				"c.t_country_orig_flag, " +
			    "t6.source,t6.small,t6.normal, " +
			    "null " +
			"from tb_home_category hc " +
			"join tb_home_item hi ON hc.t_category_id = hi.t_category_id " +
			"join tb_news2 n ON n.news_id = hi.t_link_id " +
			"join tb_country c on c.t_country_id = hi.t_country_id " +
			"join tb_image t6 on t6.image_id = n.image_id " +
			"where hc.t_feature_category_id = - 1) item order by t_item_create_time desc limit :1,:2")
	public List<Map> findAllHomeData(int startPos,int pageRows);
	
	@SQL("select count(*) from tb_home_item")
	public int findHomeItemCount();
	
	@SQL("select t1.feature_item_id,t6.source,t6.small,t6.normal," +
			"t1.zhname,t4.t_country_name_zh,t4.t_country_orig_flag," +
			"t3.zhname destName " +
			"from tb_dest_feature_item2 t1 " +
			"left join tb_dest_feature2 t2 on t2.feature_id = t1.feature_id " +
			"join tb_dest t3 on t3.dest_id = t2.dest_id " +
			"join tb_country t4 on t4.t_country_id = t3.country_id " +
			"join tb_dest_feature_category t5 on t5.t_feature_category_id = t1.feature_category_id " +
			"join tb_image t6 on t6.image_id = t1.image_id " +
			"where t5.t_feature_category_id =:1 or t5.t_feature_parent_category_id =:1 " +
			"order by t1.feature_item_id desc limit :2,:3")
	public List<Map> findFeatureItemByCategoryId(int cid,int startPos,int pageRows);
	
	@SQL("select count(i.feature_item_id) from tb_dest_feature_item2 i " +
                     "join " +
                     "tb_dest_feature_category tc ON i.feature_category_id = tc.t_feature_category_id " +
                     "where tc.t_feature_category_id =:1 or tc.t_feature_parent_category_id =:1")
	public int findFeatureItemCountInCategory(int featureCategoryId);
	
	@SQL("select th.t_home_style_id,th.t_feature_category_id,th.t_feature_category_name,"
				+ "th.t_style_orig_img,th.t_style_count,tc.t_feature_category_desc,"
				+ "tc.t_feature_category_order categoryOrder "
				+ "from tb_home_style_item th join tb_dest_feature_category tc "
				+ "on th.t_feature_category_id = tc.t_feature_category_id "
				+ "order by categoryOrder")
	public List<Map> findHomeStyleItems();
	
//	@SQL("select n.t_news_id,n.t_news_title,n.t_news_orig_img," +
//				"n.t_news_mini_img,n.t_country_id,c.t_country_name_zh,c.t_country_orig_flag,n.t_news_date " +
//				"from tb_news n join tb_country c on n.t_country_id = c.t_country_id " +
//				"order by n.t_news_date desc limit :1,:2")
//	public List<Map> findHomeNews(int startPos,int pageRows);
	
	@SQL("select t1.*,t2.*,t3.t_country_name_zh,t3.t_country_orig_flag from tb_news2 t1 join tb_image t2 on t2.image_id = t1.image_id " +
			"join tb_country t3 on t3.t_country_id = t1.country_id order by t1.country_id desc limit :1,:2")
	public List<News2VO> findNews(int startPos,int endPos);
	
	
//	@SQL("select f.t_feature_id,f.t_feature_orig_img," +
//					"f.t_feature_mini_img,f.t_feature_desc,c.t_feature_category_id,c.t_feature_category_name,"
//					+ "c.t_feature_category_order "
//					+ "from tb_dest_feature f join tb_dest_feature_category c on f.t_feature_category_id = c.t_feature_category_id "
//					+ "where f.t_feature_dest_id = :1 " +
//							" order by c.t_feature_category_order asc")
//	public List<DestFeature2VO> findDestFeatures(int destId);
	
	@SQL("select * from tb_dest_feature2 t1 join tb_image t2 on t1.image_id = t2.image_id where t1.dest_id = :1 order by t1.feature_order asc")
	public List<DestFeature2VO> findDestFeaturesWithoutContentByDestId(int destId);
	
	@SQL("select * from tb_dest where dest_id = "
					+ "(select dest_id from tb_dest_feature2 "
					+ "where feature_id =:1)")
	public DestVO findSimplestDestByFeatureId(int featureId);

}
